<?php

class Map_model extends CI_Model {
	
	
	public function get_all_tower_provider()
	{
		$query = $this->db->where('active',1);
		$query = $this->db->get('t_mtr_tower_provider2');
		if ($query->num_rows()) {
			return $query->result_array();
		} else {
			return FALSE;
		}
	}
	
	function get_koord_nom_site($site_id){
	    $lat=0;
	    $long=0;
	    $this->db->where('site_id',$site_id);
	    $query=$this->db->get('t_mtr_site_general');
	    $data_koord = $query->result_array();
	    $baris=$query->num_rows();    

	    if($baris>0){
	        foreach ($data_koord as $ko){
	            $lat=$ko["latitude"];
	            $long=$ko["longitude"];
	        }
	    }
	    $arr = array("lat" => $lat, "long" => $long);
	    return $arr;
	}
	function update_kandidat($id){
		$data = array(
               'kandidat' => 1
            );

		$this->db->where('no', $id);
		$this->db->update('t_mtr_tower_provider2', $data); 
		if($this->db->affected_rows() > 0){  
		   return true;  
		} else {  
		   return false;  
		}  

	}
	
	function update_disewa($id){
		$data = array(
               'disewa' => 1
            );

		$this->db->where('tower_provider_id', $id);
		$this->db->update('t_mtr_tower_provider', $data); 

		if($this->db->affected_rows() > 0){  
		   return true;  
		} else {  
		   return false;  
		}  
	}
	
	public function get_tower_provider($site_id)
	{
		$query = $this->db->where('site_id',$site_id);
		$query = $this->db->where('active',1);
		$query = $this->db->get('t_mtr_tower_provider');
		if ($query->num_rows()) {
			return $query->result_array();
		} else {
			return FALSE;
		}
	}

	public function get_tower_provider_filter($site_id, $status)
	{
		//1=semua, 2=Belum Kandidat, 3=Kandidat, 4=Disewa

		if($status==2){
			$query = $this->db->where('kandidat',0);
		} else if($status==3){
			$query = $this->db->where('kandidat',1);			
			$query = $this->db->where('disewa',0);			
		} else if($status==4){
			$query = $this->db->where('disewa',1);			
		}

		$query = $this->db->where('site_id',$site_id);
		$query = $this->db->where('active',1);
		$query = $this->db->get('t_mtr_tower_provider');
		if ($query->num_rows()) {
			return $query->result_array();
		} else {
			return FALSE;
		}
	}

	public function get_site_name(){
		$this->db->select('site_id, site_id_text');
		$query= $this->db->get('t_mtr_site_general');
		if ($query->num_rows()) {
			return $query->result_array();
		} else {
			return FALSE;
		}
	}	

	public function get_site_by_id($site_id=3){
		$this->db->select('site_id, site_name, latitude, longitude, site_address, site_id_text');
		$this->db->where('site_id', $site_id);
		$query= $this->db->get('t_mtr_site_general');
		if ($query->num_rows()) {
			return $query->result_array();
		} else {
			return FALSE;
		}
	}	

	public function get_tp_by_radius($site_id, $radius, $nomlat, $nomlong){
		$radiuskm=$radius/1000;
		$query=$this->db->query("(select site_id as id, 'nomsite' as tp_name, 0 as rank, site_name,site_address as address,2 as kandidat, latitude, longitude, 0 as partner_id,0 as distance from t_mtr_site_general
									where site_id=".$site_id.")
									UNION
									(select * from (SELECT a.no AS id,b.partner_name as tp_name, b.rank as rank, a.site_name,a.address,a.kandidat, a.lattitude AS latitude, a.longitude,b.partner_id as partner_id,
									( 6371 * acos( cos( radians(".$nomlat.") ) * cos( radians( a.lattitude ) ) * cos( radians( a.longitude ) - radians(".$nomlong.") ) + sin( radians(".$nomlat.") ) * sin( radians( a.lattitude ) ) ) )AS distance
									FROM t_mtr_tower_provider2 AS a LEFT JOIN t_mtr_partner AS b on a.partner_id=b.partner_id) tp
									WHERE tp.distance <".$radiuskm.")");
		//die(print_r($query->result_array()));
		if ($query->num_rows()) {
			return $query->result_array();
		} else {
			return FALSE;
		}
	}
	
	public function get_marker(){
		$this->db->select('partner_name, gis_marker, partner_id');
		$this->db->order_by('partner_name', 'asc');
		$query= $this->db->get('t_mtr_partner');
		if ($query->num_rows()) {
			return $query->result_array();
		} else {
			return FALSE;
		}
	}	
	
	public function add_kandidat($data){

	$this->db->insert('t_site_tp_kandidat', $data); 
		if($this->db->affected_rows() > 0){  
		   return true;  
		} else {  
		   return false;  
		}  

	}
	
	public function get_site_kandidat($site_id = ''){
		if (!empty($site_id)) {
			$query=$this->db->query("SELECT b.site_id AS nomsite_id,b.site_id_text, b.site_name, c.site_id, c.tp_name, c.lattitude, c.longitude, c.address, a.status, c.no
								FROM t_site_tp_kandidat as a
								LEFT JOIN t_mtr_site_general as b on a.site_id=b.site_id
								LEFT JOIN t_mtr_tower_provider2 as c on a.site_id_tp=c.no
								where a.site_id=".$site_id);
		} else {
			$query=$this->db->query("SELECT b.site_id AS nomsite_id,b.site_id_text, b.site_name, c.site_id, c.tp_name, c.lattitude, c.longitude, c.address, a.status, c.no
								FROM t_site_tp_kandidat as a
								LEFT JOIN t_mtr_site_general as b on a.site_id=b.site_id
								LEFT JOIN t_mtr_tower_provider2 as c on a.site_id_tp=c.no");
		}
		
		return $query->result_array();
	}
	
	public function get_site_kandidat_by_siteid(){
		
	}
	
	public function get_tp_kandidat_by_siteid($site_id, $nomlat, $nomlong){
	$query=$this->db->query("(SELECT site_id AS id, 'nomsite' AS tp_name, site_name,site_address AS address,2 AS kandidat, latitude, longitude, 0 AS distance FROM t_mtr_site_general
							WHERE site_id=".$site_id.")
							UNION
							(SELECT c.no AS id, c.tp_name, c.site_id AS site_name, c.address, c.kandidat, c.lattitude, c.longitude,
							(6371 * ACOS( COS( RADIANS(".$nomlat.") ) * COS( RADIANS( c.lattitude ) ) * COS( RADIANS( c.longitude ) - RADIANS(".$nomlong.") ) + 
							SIN( RADIANS(".$nomlat.") ) * SIN( RADIANS( c.lattitude ) ) ) )  AS distance									
							FROM t_site_tp_kandidat AS a
							LEFT JOIN t_mtr_site_general AS b on a.site_id=b.site_id
							LEFT JOIN t_mtr_tower_provider2 AS c on a.site_id_tp=c.no
							WHERE a.site_id=".$site_id.")");	
		if ($query->num_rows()) {
			return $query->result_array();
		} else {
			return FALSE;
		}
	}

	public function get_tp_by_id($tp_id){
		$this->db->where('no', $tp_id);
		$query= $this->db->get('t_mtr_tower_provider2');
		if ($query->num_rows()) {
			return $query->result_array();
		} else {
			return FALSE;
		}
	}	

	function update_status_verified_tp($tp_id,$nomsite_id){
		$data = array(
               'status' => 2,
               'updated_by' => $this->session->userdata('username'),
               'updated_on' => date('Y-m-d H:i:s')
            );

		$this->db->where('site_id_tp', $tp_id);
		$this->db->where('site_id', $nomsite_id);
		$this->db->update('t_site_tp_kandidat', $data); 
		if($this->db->affected_rows() > 0){  
		   return true;  
		} else {  
		   return false;  
		}  

	}
	
	function update_status_pemenang_tp($tp_id,$nomsite_id){
		$data = array(
               'status' => 3,
               'updated_by' => $this->session->userdata('username'),
               'updated_on' => date('Y-m-d H:i:s')
            );

		$this->db->where('site_id_tp', $tp_id);
		$this->db->where('site_id', $nomsite_id);
		$this->db->update('t_site_tp_kandidat', $data); 
		if($this->db->affected_rows() > 0){  
		   return true;  
		} else {  
		   return false;  
		}  

	}

	function cek_site_kandidat($site_id, $site_id_tp){
		$this->db->where('site_id', $site_id);
		$this->db->where('site_id_tp', $site_id_tp);
		$query= $this->db->get('t_site_tp_kandidat');
		if ($query->num_rows()>0) {
			return TRUE;
		} else {
			return FALSE;
		}
		
	}
	
	public function get_site_link(){
		$query= $this->db->get('t_site_link');
		if ($query->num_rows()) {
			return $query->result_array();
		} else {
			return null;
		}
	}	

}